﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using DHQGHN.PM2.Models;
using Newtonsoft.Json;
using System.Runtime.Serialization.Json;
using System.Net;
using System.IO;
using System.Text;
using DHQGHN.PM2.ViewModels;
using System.Web.Script.Serialization;
using System.ComponentModel;
using System.Data;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Core;
using System.Drawing;

namespace DHQGHN.PM2.Controllers
{
    //Quyvd khởi tạo list model để in excel
    public class ModelHouseHoldList
    {
        public string householdNumber { get; set; }
        public string householdName { get; set; }
        public string areaname { get; set; }
        public string Numofhhmember { get; set; }
        public string hhHeadmobile { get; set; }
        public string hhHeademail { get; set; }
        public string AddreaDetails { get; set; }
        public string GpsLat { get; set; }
        public string GpsLong { get; set; }
        public string GpsAccuracy { get; set; }
        public string ServeymethodName { get; set; }
        public string onlineSurvey { get; set; }
    }

    [Authorize]
    public class HouseHoldController : BaseController
    {
        log4net.ILog logger = log4net.LogManager.GetLogger(typeof(HouseHoldController));

        #region Bind data
        public void BindAdm()
        {
            try
            {
                AdminisUnitModel adminList = new AdminisUnitModel();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByID");
                    wc.Encoding = Encoding.UTF8;
                    string url = adminUnitServiceUrl + "GetByID" + "/" + MyUser.AdminID;
                    string adminDownload = wc.DownloadString(url);
                    adminList = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(adminDownload).Result;
                }

                ViewBag.ADM_UNIT = adminList.COMMUNE_NAME + "  -  " + adminList.DISTRICT_NAME + "  -  " + adminList.PROVINCE_NAME;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        public void BindAddress()
        {
            try
            {
                List<AddressModel> addressList = new List<AddressModel>();

                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByAdminCode");
                    wc.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();

                    string addressDownload = wc.DownloadString(addressServiceUrl + "GetByAdminCode" + "/" + MyUser.AdminCode);
                    addressList = JsonConvert.DeserializeObjectAsync<List<AddressModel>>(addressDownload).Result;
                    foreach (var address in addressList)
                    {
                        if (address != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = address.ADDRESS_ID,
                                Text = address.ADDRESS_DETAILS
                            });
                    }
                    ViewBag.Address = lstItems;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public void BindArea()
        {
            try
            {
                List<Models.AreaModel> areaList = new List<Models.AreaModel>();

                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByUserName");
                    wc.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();

                    string areaDownload = wc.DownloadString(areaServiceUrl + "GetByUserName" + "/" + MyUser.UserName);
                    areaList = JsonConvert.DeserializeObjectAsync<List<Models.AreaModel>>(areaDownload).Result;
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_ID,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewBag.Area = lstItems;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public void BindSurveyMethod()
        {
            try
            {
                List<MasterListModel> surveymethodList = new List<MasterListModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetAllByListName");
                    wc.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();

                    string serveyDownload = wc.DownloadString(masterListServiceUrl + "GetAllByListName" + "/" + "SURVEY_METHOD");
                    surveymethodList = JsonConvert.DeserializeObjectAsync<List<MasterListModel>>(serveyDownload).Result;
                    foreach (var method in surveymethodList)
                    {
                        if (method != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = method.CodeID,
                                Text = method.Name
                            });
                    }
                    ViewBag.SurveyMethod = lstItems;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public void BindAdminType()
        {
            List<SelectListItem> lstItems = new List<SelectListItem>();
            lstItems.Add(new SelectListItem
                {
                    Text = "Đường phố",
                    Value = "DP"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Ngõ",
                    Value = "NG",
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Ngách",
                    Value = "NGH"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Khu chung cư",
                    Value = "CC"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Khu tập thể",
                    Value = "TT"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Dãy nhà",
                    Value = "DN"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Tòa nhà",
                    Value = "TA"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Tổ dân cư",
                    Value = "TO"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Thôn",
                    Value = "TH"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Ấp",
                    Value = "AP"
                });
            lstItems.Add(new SelectListItem
                {
                    Text = "Bản",
                    Value = "BA"
                });
            ViewBag.AdminType = lstItems;
        }

        //
        // Get AdmUnitExt
        public SelectList BindAdminUnitExt(string parentID, string type)
        {
            try
            {
                List<AdministrativeUnitExtModels> adminList = new List<AdministrativeUnitExtModels>();

                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetAdminExtDropDown");
                    wc.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    string url = adminUnitExtServiceUrl + "GetAdminExtDropDown" + "/" + MyUser.AdminCode + "/" + parentID + "/" + type;
                    string adminDownload = wc.DownloadString(url);
                    adminList = JsonConvert.DeserializeObjectAsync<List<AdministrativeUnitExtModels>>(adminDownload).Result;
                    foreach (var ade in adminList)
                    {
                        if (ade != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = ade.ADM_UNIT_EXT_ID,
                                Text = ade.NAME
                            });
                    }
                    return new SelectList(lstItems, "Value", "Text");

                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [AjaxValidateAntiForgeryToken]
        public JsonResult ddlAdminisUnitLevel1(string parentID, string type)
        {
            try
            {
                BangKeViewModel bangKe = new BangKeViewModel();
                bangKe.ADM_UNIT_EXT_TYPE = type;
                if (type == "NG" || type == "NGH")
                    return Json(BindAdminUnitExt(parentID, "DP"));
                else if (type == "DN")
                    return Json(BindAdminUnitExt(parentID, "TT"));
                else if (type == "TA")
                    return Json(BindAdminUnitExt(parentID, "CC"));
                else
                    return Json(BindAdminUnitExt(parentID, type));
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [AjaxValidateAntiForgeryToken]
        public JsonResult ddlAdminisUnitLevel2(string parentID, string type)
        {
            try
            {
                if (type == "NGH")
                    return Json(BindAdminUnitExt(parentID, "NG"));
                else
                    return Json(BindAdminUnitExt(parentID, type));
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [AjaxValidateAntiForgeryToken]
        public JsonResult ddlAdminisUnitLevel3(string parentID, string type)
        {
            try
            {
                return Json(BindAdminUnitExt(parentID, type));
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        //
        // Get AdmUnit
        public SelectList BindAdminUnit(string parentCode, string level)
        {
            try
            {
                List<AdminisUnitModel> adminList = new List<AdminisUnitModel>();

                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByIdParentTablet");
                    wc.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    string url = adminUnitServiceUrl + "GetByIdParentTablet" + "/" + parentCode + "/" + level;
                    string adminDownload = wc.DownloadString(url);
                    adminList = JsonConvert.DeserializeObjectAsync<List<AdminisUnitModel>>(adminDownload).Result;
                    foreach (var ad in adminList)
                    {
                        if (ad != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = ad.CODE,
                                Text = ad.ADM_NAME
                            });
                    }
                    return new SelectList(lstItems, "Value", "Text");
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [AjaxValidateAntiForgeryToken]
        public JsonResult ddlAdmUnitLevel1()
        {
            try
            {
                return Json(BindAdminUnit("-1", "1"));
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [AjaxValidateAntiForgeryToken]
        public JsonResult ddlAdmUnitLevel2(string parentCode)
        {
            try
            {
                return Json(BindAdminUnit(parentCode, "2"));
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [AjaxValidateAntiForgeryToken]
        public JsonResult ddlAdmUnitLevel3(string parentCode)
        {
            try
            {
                return Json(BindAdminUnit(parentCode, "3"));
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        public List<MappingPTDTViewModel> SetPTDT_Count(string AdminCode)
        {
            MappingPTDTViewModel mapPost = new MappingPTDTViewModel();
            mapPost.CODE = AdminCode;

            List<MappingPTDTViewModel> mapGet = new List<MappingPTDTViewModel>();
            try
            {
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "SetPTDT_Count");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(mapPost);
                    string householdDownload = wc.UploadString(houseHoldServiceUrl + "SetPTDT_Count", "POST", json);

                    mapGet = JsonConvert.DeserializeObjectAsync<List<MappingPTDTViewModel>>(householdDownload).Result;
                }

                foreach (var map in mapGet)
                {
                    if (map.SURVEY_METHOD_ID != null)
                    {
                        List<string> method = map.SURVEY_METHOD_ID.Split(new char[] { ',' }).ToList();
                        if (!method.All(s => s == method.First()))
                        {
                            map.SURVEY_METHOD_ID = "-1";
                        }
                        else
                        {
                            map.SURVEY_METHOD_ID = method.First();
                        }
                    }
                    else
                    {
                        map.SURVEY_METHOD_ID = "-1";
                        map.RECODE_COUNT = 0;
                    }
                }
                return mapGet;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }

        }

        public List<MappingPTDTViewModel> ChangePTDT_Count(string AdminCode)
        {
            try
            {
                MappingPTDTViewModel mapPost = new MappingPTDTViewModel();
                mapPost.CODE = AdminCode;
                List<PARAMETER> Param = new List<PARAMETER>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetEndDate");
                    wc.Encoding = Encoding.UTF8;
                    string url = houseHoldServiceUrl + "GetEndDate";
                    string ParamDownload = wc.DownloadString(url);
                    Param = JsonConvert.DeserializeObjectAsync<List<PARAMETER>>(ParamDownload).Result;
                }
                mapPost.ONLINE_ENDDATE = Param[0].ONLINE_ENDDATE;
                List<MappingPTDTViewModel> mapGet = new List<MappingPTDTViewModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "ChangePTDT_Count");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(mapPost);
                    string householdDownload = wc.UploadString(houseHoldServiceUrl + "ChangePTDT_Count", "POST", json);

                    mapGet = JsonConvert.DeserializeObjectAsync<List<MappingPTDTViewModel>>(householdDownload).Result;
                }

                foreach (var map in mapGet)
                {
                    if (map.SURVEY_METHOD_ID != null)
                    {
                        List<string> method = map.SURVEY_METHOD_ID.Split(new char[] { ',' }).ToList();
                        if (!method.All(s => s == method.First()))
                        {
                            map.SURVEY_METHOD_ID = "-1";
                        }
                        else
                        {
                            map.SURVEY_METHOD_ID = method.First();
                        }
                    }
                    else
                    {
                        map.SURVEY_METHOD_ID = "-1";
                        map.RECODE_COUNT = 0;
                    }

                    if (map.CheckMethod == null)
                    {
                        map.RECODE_COUNT = 0;
                    }
                }
                return mapGet;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }

        }
        #endregion
        #region Show table
        public ActionResult IndexHouseHoldPV(string HOUSEHOLD_CODE, int? HOUSEHOLD_NUMBER, string HOUSEHOLD_NAME, int? NUM_OF_HH_MEMBER,
            string AREA_ID, string ADDRESS_ID, string SURVEY_METHOD_ID, int? page)
        {

            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[104] = "active";
                ViewBag.ActiveMenus[113] = "active";

                BindArea();
                BindSurveyMethod();
                BindAddress();
                BindAdm();
                if (String.IsNullOrEmpty(AREA_ID))
                    AREA_ID = null;
                if (String.IsNullOrEmpty(ADDRESS_ID))
                    ADDRESS_ID = null;
                if (String.IsNullOrEmpty(SURVEY_METHOD_ID))
                    SURVEY_METHOD_ID = null;
                SearchHouseHoldModel search = new SearchHouseHoldModel();
                search.HOUSEHOLD_CODE = HOUSEHOLD_CODE;
                search.HOUSEHOLD_NAME = HOUSEHOLD_NAME;
                search.HOUSEHOLD_NUMBER = HOUSEHOLD_NUMBER;
                search.NUM_OF_HH_MEMBER = NUM_OF_HH_MEMBER;
                search.AREA_ID = AREA_ID;
                search.ADDRESS_ID = ADDRESS_ID;
                search.SURVEY_METHOD_ID = SURVEY_METHOD_ID;
                search.CODE = MyUser.AdminCode;
                if (page == null)
                {
                    search.PageIndex = 1;
                }
                else
                    search.PageIndex = page;

                search.PageSize = 10;


                List<HouseHoldModel> householdList = new List<HouseHoldModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "SearchHouseHold");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);
                    string householdDownload = wc.UploadString(houseHoldServiceUrl + "SearchHouseHold", "POST", json);

                    householdList = JsonConvert.DeserializeObjectAsync<List<HouseHoldModel>>(householdDownload).Result;
                }
                if (page != 0 && householdList.Count == 0)
                {
                    search.PageIndex = 1;
                    using (WebClient wc = new WebClient())
                    {
                        Helper.SetHeaderInfo(wc, "SearchHouseHold");
                        wc.Encoding = Encoding.UTF8;
                        wc.Headers["Content-type"] = "application/json; charset=utf-8";
                        var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);
                        string householdDownload = wc.UploadString(houseHoldServiceUrl + "SearchHouseHold", "POST", json);

                        householdList = JsonConvert.DeserializeObjectAsync<List<HouseHoldModel>>(householdDownload).Result;
                    }
                }

                ViewData["householdName"] = HOUSEHOLD_NAME;
                ViewData["numofhhMember"] = NUM_OF_HH_MEMBER;
                ViewData["addressId"] = ADDRESS_ID;
                ViewData["householdNumber"] = HOUSEHOLD_NUMBER;
                ViewData["areaId"] = AREA_ID;
                ViewData["surveyId"] = SURVEY_METHOD_ID;
                //]Quyvd
                return View(householdList);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        [ValidateAntiForgeryToken()]
        public ActionResult IndexHouseHoldPV(SearchHouseHoldModel search)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[104] = "active";
            ViewBag.ActiveMenus[113] = "active";

            try
            {
                if (search == null)
                {
                    return View();
                }
                search.PageIndex = search.PageIndex ?? 1;

                return RedirectToAction("IndexHouseHoldPV", new
                {
                    search.HOUSEHOLD_CODE,
                    search.HOUSEHOLD_NUMBER,
                    search.HOUSEHOLD_NAME,
                    search.NUM_OF_HH_MEMBER,
                    search.AREA_ID,
                    search.ADDRESS_ID,
                    search.SURVEY_METHOD_ID,
                    search.PageIndex
                });
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return Content("Có lỗi xảy ra trong quá trình xử lý dữ liệu: " + ex.Message + ".", "text/html", UTF8Encoding.UTF8);
            }

        }

        #endregion
        #region Create new

        public ActionResult LapBangKeVM()
        {
            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[104] = "active";
                ViewBag.ActiveMenus[112] = "active";
                BindArea();
                BindAdminType();
                BindSurveyMethod();
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        [ValidateAntiForgeryToken()]
        public ActionResult LapBangKeVM(BangKeViewModel BangKe)
        {
            try
            {
                List<PARAMETER> Param = new List<PARAMETER>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetEndDate");
                    wc.Encoding = Encoding.UTF8;
                    string url = houseHoldServiceUrl + "GetEndDate";
                    string ParamDownload = wc.DownloadString(url);
                    Param = JsonConvert.DeserializeObjectAsync<List<PARAMETER>>(ParamDownload).Result;
                }
                if (Param != null && Param.Count > 0)
                {
                    BangKe.ENDDATE = Param[0].ONLINE_ENDDATE;
                }
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[104] = "active";
                ViewBag.ActiveMenus[112] = "active";
                BindArea();
                BindAdminType();
                BindSurveyMethod();
                using (WebClient wc = new WebClient())
                {
                    if (ModelState.IsValid)
                    {
                        using (MemoryStream ms = new MemoryStream())
                        {
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(BangKeViewModel));
                            if (BangKe != null)
                            {
                                BangKe.COUNTRY_ID = "VN";
                            }
                            Helper.SetHeaderInfo(wc, "AddHouseHoldAddress");
                            serializerToUpload.WriteObject(ms, BangKe);
                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(houseHoldServiceUrl + "AddHouseHoldAddress", "POST", ms.ToArray());

                            LogStream.Write<BangKeViewModel>(MyUser.UserName,
                                CommonData.AppName,
                                GetFunction(),
                                "Thêm mới bảng kê - LapBangKeVM", BangKe);
                            return RedirectToAction("IndexHouseHoldPV");
                        }
                    }
                    return View(BangKe);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        #endregion
        #region Detail
        public ActionResult DetailHouseHold(string HOUSEHOLD_ID)
        {
            //BindServeyMethod();
            try
            {
                BindArea();
                BangKeViewModel household = new BangKeViewModel();
                AdministrativeUnitExtModels ade = new AdministrativeUnitExtModels();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetHouseHoldAddressByID");

                    wc.Encoding = Encoding.UTF8;
                    string householdDownload = wc.DownloadString(houseHoldServiceUrl + "GetHouseHoldAddressByID" + "/" + HOUSEHOLD_ID);
                    household = JsonConvert.DeserializeObjectAsync<BangKeViewModel>(householdDownload).Result;

                    if (household.ADM_UNIT_EXT_ID != null)
                    {
                        using (WebClient wc1 = new WebClient())
                        {
                            Helper.SetHeaderInfo(wc1, "GetByID");
                            string adeDownload = wc1.DownloadString(adminUnitExtServiceUrl + "GetByID" + "/" + household.ADM_UNIT_EXT_ID);
                            ade = JsonConvert.DeserializeObjectAsync<AdministrativeUnitExtModels>(adeDownload).Result;

                            household.ADM_UNIT_EXT_NAME = ade.NAME;
                        }
                    }
                }

                return View(household);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        #endregion
        #region Edit
        public ActionResult SuaBangKeVM(string HOUSEHOLD_ID)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[104] = "active";
            ViewBag.ActiveMenus[113] = "active";

            try
            {
                BindArea();
                BindAdminType();
                BindSurveyMethod();
                BangKeViewModel bangKe = new BangKeViewModel();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetHouseHoldAddressByID");
                    wc.Encoding = Encoding.UTF8;
                    string bangKeDownload = wc.DownloadString(houseHoldServiceUrl + "GetHouseHoldAddressByID" + "/" + HOUSEHOLD_ID);
                    bangKe = JsonConvert.DeserializeObjectAsync<BangKeViewModel>(bangKeDownload).Result;
                    if (bangKe != null)
                    {
                        string adm_unit_extDownload = wc.DownloadString(adminUnitExtServiceUrl + "GetByID" + "/" + bangKe.ADM_UNIT_EXT_ID);
                        AdministrativeUnitExtModels adm = JsonConvert.DeserializeObjectAsync<AdministrativeUnitExtModels>(adm_unit_extDownload).Result;
                        bangKe.ADM_UNIT_EXT_TYPE = adm.ADM_UNIT_EXT_TYPE;
                        bangKe.ADM_UNIT_EXT_NAME = adm.NAME;
                        bangKe.PARENT_ID = adm.PARENT_ID;

                        if (bangKe.ADM_UNIT_EXT_TYPE == "NGH")
                        {
                            using (WebClient wc1 = new WebClient())
                            {
                                Helper.SetHeaderInfo(wc1, "GetByID");
                                string adm_unit_extDownload2 = wc1.DownloadString(adminUnitExtServiceUrl + "GetByID" + "/" + bangKe.PARENT_ID);
                                AdministrativeUnitExtModels adm2 = JsonConvert.DeserializeObjectAsync<AdministrativeUnitExtModels>(adm_unit_extDownload2).Result;

                                bangKe.PARENT_ID2 = adm2.PARENT_ID;
                            }
                        }
                    }

                }

                return View(bangKe);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        [ValidateAntiForgeryToken()]
        public ActionResult SuaBangKeVM(BangKeViewModel BangKe)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[104] = "active";
            ViewBag.ActiveMenus[113] = "active";

            try
            {
                BindArea();
                BindAdminType();
                BindSurveyMethod();
                using (WebClient wc = new WebClient())
                {
                    if (ModelState.IsValid)
                    {
                        if (BangKe != null)
                        {
                            BangKe.COUNTRY_ID = "VN";

                            using (MemoryStream ms = new MemoryStream())
                            {
                                Helper.SetHeaderInfo(wc, "EditHouseHoldAddress");
                                DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(BangKeViewModel));
                                serializerToUpload.WriteObject(ms, BangKe);
                                wc.Headers["Content-type"] = "application/json; charset=utf-8";
                                wc.UploadData(houseHoldServiceUrl + "EditHouseHoldAddress", "POST", ms.ToArray());
                                LogStream.Write<BangKeViewModel>(MyUser.UserName,
                                   CommonData.AppName,
                                   GetFunction(),
                                   "Cập nhật bảng kê - SuaBangKeVM", BangKe);
                                ms.Dispose();
                                return RedirectToAction("IndexHouseHoldPV");
                            }
                        }
                    }
                    return View(BangKe);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        #endregion
        #region Delete
        public ActionResult DeleteHouseHold(string HOUSEHOLD_ID)
        {
            try
            {
                HouseHoldModel household = new HouseHoldModel();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByID");
                    wc.Encoding = Encoding.UTF8;
                    string householdDownload = wc.DownloadString(houseHoldServiceUrl + "GetByID" + "/" + HOUSEHOLD_ID);
                    household = JsonConvert.DeserializeObjectAsync<HouseHoldModel>(householdDownload).Result;

                    //string areaDownload = wc.DownloadString(areaServiceUrl + "GetByID" + "/" + household.AREA_ID);
                    //AreaModel area = JsonConvert.DeserializeObjectAsync<AreaModel>(areaDownload).Result;
                    //household.AREA_ID_NAME = area.AREA_NAME;
                }

                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByID");

                    string areaDownload = wc.DownloadString(areaServiceUrl + "GetByID" + "/" + household.AREA_ID);
                    AreaModel area = JsonConvert.DeserializeObjectAsync<AreaModel>(areaDownload).Result;
                    household.AREA_ID_NAME = area.AREA_NAME;
                }

                return PartialView(household);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public ActionResult DeleteHouseHoldConfirmed(string HOUSEHOLD_ID)
        {
            try
            {
                using (WebClient wc = new WebClient())
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        Helper.SetHeaderInfo(wc, "DeleteHouseHold");
                        DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(HouseHoldModel));
                        serializerToUpload.WriteObject(ms, HOUSEHOLD_ID);
                        wc.Headers["Content-type"] = "application/json; charset=utf-8";
                        wc.UploadData(houseHoldServiceUrl + "DeleteHouseHold", "POST", ms.ToArray());

                        LogStream.Write(MyUser.UserName,
                               CommonData.AppName,
                               GetFunction(),
                               CommonData.GetListId(new string[] { HOUSEHOLD_ID }),
                               "Xóa bảng kê - LapBangKeVM");

                        return RedirectToAction("IndexHouseHoldPV");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        #endregion
        #region Gán phương thức điều tra theo địa bàn
        public ActionResult PhuongThucDT(MappingPTDTViewModel map)
        {
            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[102] = "active";
                ViewBag.ActiveMenus[121] = "active";
                if (map != null)
                {
                    if (map.CODE != null)
                    {
                        map.LEVELS = "3";
                        if (map.CODE.Length >= 2)
                            ViewBag.Level1 = map.CODE.Substring(0, 2);
                        if (map.CODE.Length >= 5)
                            ViewBag.Level2 = map.CODE.Substring(0, 5);
                        if (map.CODE.Length == 10)
                            ViewBag.Level3 = map.CODE.Substring(0, 10);
                    }
                    else
                    {
                        ViewBag.Level1 = null;
                        ViewBag.Level2 = null;
                        ViewBag.Level3 = null;
                    }
                }
                return View(map);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [AjaxValidateAntiForgeryToken]
        public ActionResult MappingPTDT(string parentCode)
        {
            try
            {
                MappingPTDTViewModel map = new MappingPTDTViewModel();

                map.AdmUnitSelected = SetPTDT_Count(parentCode);
                map.CODE = parentCode;
                return PartialView(map);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [HttpPost]
        [ValidateAntiForgeryToken()]
        public ActionResult PhuongThucDTPost(MappingPTDTViewModel map)
        {
            if (ModelState.IsValid)
            {
                try
                {
                    using (WebClient wc = new WebClient())
                    {
                        using (MemoryStream ms = new MemoryStream())
                        {
                            Helper.SetHeaderInfo(wc, "MappingPTDT");
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(MappingPTDTViewModel));
                            serializerToUpload.WriteObject(ms, map);
                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(houseHoldServiceUrl + "MappingPTDT", "POST", ms.ToArray());
                        }

                    }
                }
                catch (Exception ex)
                {
                    logger.Error(ex.ToString());
                    throw;
                }
            }
            return RedirectToAction("PhuongThucDT", map);
        }
        #endregion
        #region Chuyển đổi phương thức điều tra
        public void GetEndDate()
        {
            try
            {
                List<PARAMETER> Param = new List<PARAMETER>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetEndDate");
                    wc.Encoding = Encoding.UTF8;
                    string url = houseHoldServiceUrl + "GetEndDate";
                    string ParamDownload = wc.DownloadString(url);
                    Param = JsonConvert.DeserializeObjectAsync<List<PARAMETER>>(ParamDownload).Result;
                }

                ViewBag.OnlineEnd = Param[0].ONLINE_ENDDATE;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public ActionResult ChangePTDT(MappingPTDTViewModel map)
        {
            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[102] = "active";
                ViewBag.ActiveMenus[128] = "active";
                GetEndDate();
                if (map != null)
                {
                    if (map.CODE != null)
                    {
                        map.LEVELS = "3";
                        if (map.CODE.Length >= 2)
                            ViewBag.Level1 = map.CODE.Substring(0, 2);
                        if (map.CODE.Length >= 5)
                            ViewBag.Level2 = map.CODE.Substring(0, 5);
                        if (map.CODE.Length == 10)
                            ViewBag.Level3 = map.CODE.Substring(0, 10);
                    }
                    else
                    {
                        ViewBag.Level1 = null;
                        ViewBag.Level2 = null;
                        ViewBag.Level3 = null;
                    }
                }
                return View(map);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [AjaxValidateAntiForgeryToken]
        public ActionResult ChangingPTDT(string parentCode)
        {
            try
            {
                MappingPTDTViewModel map = new MappingPTDTViewModel();

                map.AdmUnitSelected = ChangePTDT_Count(parentCode);
                map.CODE = parentCode;
                return PartialView(map);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        [HttpPost]
        [ValidateAntiForgeryToken()]
        public ActionResult ChangePTDTPost(MappingPTDTViewModel map)
        {
            if (ModelState.IsValid)
            {
                try
                {
                    List<PARAMETER> Param = new List<PARAMETER>();
                    using (WebClient wc = new WebClient())
                    {
                        Helper.SetHeaderInfo(wc, "GetEndDate");
                        wc.Encoding = Encoding.UTF8;
                        string url = houseHoldServiceUrl + "GetEndDate";
                        string ParamDownload = wc.DownloadString(url);
                        Param = JsonConvert.DeserializeObjectAsync<List<PARAMETER>>(ParamDownload).Result;
                    }
                    if (map != null)
                    {
                        map.ONLINE_ENDDATE = Param[0].ONLINE_ENDDATE;
                    }

                    using (WebClient wc = new WebClient())
                    {
                        using (MemoryStream ms = new MemoryStream())
                        {
                            Helper.SetHeaderInfo(wc, "ChangingPTDT");
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(MappingPTDTViewModel));
                            serializerToUpload.WriteObject(ms, map);
                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(houseHoldServiceUrl + "ChangingPTDT", "POST", ms.ToArray());

                            LogStream.Write<MappingPTDTViewModel>(MyUser.UserName,
                                CommonData.AppName,
                                GetFunction(),
                                "Thay đổi phương thức điều tra - ChangePTDTPost", map);
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.Error(ex.ToString());
                    throw;
                }
            }
            return RedirectToAction("ChangePTDT", map);
        }
        #endregion
        #region In excel
        public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)
        {
            try
            {
                PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

                DataTable table = new DataTable();

                foreach (PropertyDescriptor prop in properties)
                    table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                if (data != null)
                {
                    foreach (T item in data)
                    {
                        DataRow row = table.NewRow();

                        foreach (PropertyDescriptor prop in properties)
                            row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

                        table.Rows.Add(row);
                    }
                }
                return table;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpGet]
        public FileResult ExportData(int? HOUSEHOLD_NUMBER, string HOUSEHOLD_NAME, int? NUM_OF_HH_MEMBER,
            string AREA_ID, string ADDRESS_ID, string SURVEY_METHOD_ID, int? page)
        {
            if (String.IsNullOrEmpty(AREA_ID) || AREA_ID == "undefined")
                AREA_ID = null;
            if (String.IsNullOrEmpty(ADDRESS_ID) || ADDRESS_ID == "undefined")
                ADDRESS_ID = null;
            if (String.IsNullOrEmpty(SURVEY_METHOD_ID) || SURVEY_METHOD_ID == "undefined")
                SURVEY_METHOD_ID = null;
            if (String.IsNullOrEmpty(HOUSEHOLD_NAME) || HOUSEHOLD_NAME == "undefined")
                HOUSEHOLD_NAME = null;

            try
            {
                SearchHouseHoldModel search = new SearchHouseHoldModel();
                search.HOUSEHOLD_CODE = String.Empty;
                search.HOUSEHOLD_NAME = HOUSEHOLD_NAME;
                search.HOUSEHOLD_NUMBER = HOUSEHOLD_NUMBER;
                search.NUM_OF_HH_MEMBER = NUM_OF_HH_MEMBER;
                search.AREA_ID = AREA_ID;
                search.ADDRESS_ID = ADDRESS_ID;
                search.SURVEY_METHOD_ID = SURVEY_METHOD_ID;
                search.CODE = MyUser.AdminCode;
                string TenDB = MyUser.AdminName;
                ViewBag.TenDB = TenDB;
                if (page == null)
                {
                    search.PageIndex = 1;
                }
                else
                    search.PageIndex = page;

                search.PageSize = 999999;


                List<HouseHoldModel> householdList = new List<HouseHoldModel>();
                List<BangKeViewModel> household = new List<BangKeViewModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "SearchHouseHold");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);
                    string householdDownload = wc.UploadString(houseHoldServiceUrl + "SearchHouseHold", "POST", json);
                    householdList = JsonConvert.DeserializeObjectAsync<List<Models.HouseHoldModel>>(householdDownload).Result;

                    using (WebClient wc1 = new WebClient())
                    {
                        Helper.SetHeaderInfo(wc1, "GetHouseHoldAddressByID");
                        foreach (var h in householdList)
                        {
                            string householdDownloadALL = wc1.DownloadString(houseHoldServiceUrl + "GetHouseHoldAddressByID" + "/" + h.HOUSEHOLD_ID);
                            household.Add(JsonConvert.DeserializeObjectAsync<BangKeViewModel>(householdDownloadALL).Result);
                        }
                    }

                }
                //tao ra 1 list moi khi muon lay mot so cot nhat dinh
                //var list = new List<Tuple<string, string, string, string, string, string, string, string, string, string, string>>();

                List<ModelHouseHoldList> householdListExcel = new List<ModelHouseHoldList>();

                foreach (BangKeViewModel h in household)
                {
                    try
                    {
                        householdListExcel.Add(new ModelHouseHoldList()
                        {
                            householdNumber = h.HOUSEHOLD_NUMBER.ToString(), //số hộ
                            householdName = h.HOUSEHOLD_NAME, //Tên chủ hộ
                            areaname = h.AREA_NAME,
                            Numofhhmember = h.NUM_OF_HH_MEMBER == null ? String.Empty : h.NUM_OF_HH_MEMBER.ToString(), //số nhân khẩu
                            hhHeadmobile = h.HH_HEAD_MOBILE, //SĐt chủ hộ
                            hhHeademail = h.HH_HEAD_EMAIL, //Email chủ hộ
                            AddreaDetails = h.ADDRESS_DETAILS, //Địa chỉ chủ hộ
                            GpsLat = h.GPS_LATITUDE, //Vĩ độ
                            GpsLong = h.GPS_LONGITUDE, //Kinh độ
                            GpsAccuracy = h.GPS_ACCURACY, //Tọa độ tâm
                            ServeymethodName = h.SURVEY_METHOD_NAME, //Phương thức
                            onlineSurvey = h.ONLINE_SURVEY_STATUS == "0" ? "Chưa hoàn thành" : "Đã hoàn thành"
                        });
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.ToString());
                        throw;
                    }

                }
                //tao 1 vung nhớ

                //in ra file excel
                DataTable dt = ConvertToDataTable(householdListExcel);
                //ExportExcel(dt,"HaNoi");
                //string TenDB = "HaNoi";
                using (var excelPackage = new ExcelPackage())
                {
                    excelPackage.Workbook.Properties.Author = "MR. Quy";
                    excelPackage.Workbook.Properties.Title = "Export excel";
                    var sheet = excelPackage.Workbook.Worksheets.Add("Bảng kê");
                    // output a line for the headers
                    //CreateHeader(sheet);
                    sheet.Name = "ThongTinBangKe";
                    // all indexes start at 1
                    sheet.Cells["A1"].Value = "Thông tin bảng kê";
                    sheet.Cells["A1:M1"].Merge = true;
                    sheet.Cells["A1:M1"].Style.Font.Size = 18;
                    sheet.Cells["A1:M1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells["A1:M1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells["A1:M1"].Style.Font.Bold = true;
                    sheet.Cells["A1:M1"].Style.WrapText = true;

                    DataTable table = new DataTable();
                    table.Columns.Add("STT", typeof(string));

                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        DataRow row = table.NewRow();
                        row["STT"] = i;
                        table.Rows.Add(row);
                    }

                    using (var rng = sheet.Cells["A2:M2"])
                    {
                        rng.Style.Font.Bold = true;
                        rng.Style.WrapText = true;
                        rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        rng.Style.Font.Size = 10;
                    }

                    sheet.Cells["A4"].Value = "STT";
                    sheet.Cells["B4"].Value = "Số hộ";
                    sheet.Cells["C4"].Value = "Tên chủ hộ";
                    sheet.Cells["D4"].Value = "Địa bàn";
                    sheet.Cells["E4"].Value = "Số nhân khẩu";
                    sheet.Cells["F4"].Value = "SĐT chủ hộ";
                    sheet.Cells["G4"].Value = "Email chủ hộ";
                    sheet.Cells["H4"].Value = "Địa chỉ chủ hộ";
                    sheet.Cells["I4"].Value = "Vĩ độ";
                    sheet.Cells["J4"].Value = "Kinh độ";
                    sheet.Cells["K4"].Value = "Tọa độ tâm";
                    sheet.Cells["L4"].Value = "Phương thức";
                    sheet.Cells["M4"].Value = "Trạng thái";

                    sheet.Cells["A4:M4"].Style.Font.Size = 12;
                    sheet.Cells["A4:M4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells["A4:M4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells["A4:M4"].Style.Font.Bold = true;

                    sheet.Cells["A2"].Value = "(" + TenDB + ")";
                    sheet.Cells["A2:M2"].Merge = true;
                    sheet.Cells["A2:M2"].Style.Font.Size = 10;
                    sheet.Cells["A2:M2"].Style.Font.Color.SetColor(Color.Red);
                    sheet.Cells["A2:M2"].Style.Font.Bold = true;
                    sheet.Cells["A2:M2"].Style.WrapText = true;

                    sheet.Cells["B5"].LoadFromDataTable(dt, true);
                    sheet.Cells["A5"].LoadFromDataTable(table, true);
                    sheet.DeleteRow(5);

                    for (var j = 0; j < dt.Columns.Count + 1; j++)
                    {
                        for (var i = 0; i <= dt.Rows.Count; i++)
                        {
                            sheet.Cells[i + 4, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        }
                    }

                    sheet.Cells["A1:AN"].AutoFitColumns();
                    sheet.Cells["B1:BN"].AutoFitColumns();
                    sheet.Cells["C1:CN"].AutoFitColumns();
                    sheet.Cells["D1:DN"].AutoFitColumns();
                    sheet.Cells["E1:EN"].AutoFitColumns();
                    sheet.Cells["F1:FN"].AutoFitColumns();
                    sheet.Cells["G1:GN"].AutoFitColumns();
                    sheet.Cells["H1:HN"].AutoFitColumns();
                    sheet.Cells["I1:IN"].AutoFitColumns();
                    sheet.Cells["J1:JN"].AutoFitColumns();
                    sheet.Cells["K1:KN"].AutoFitColumns();
                    sheet.Cells["L1:LN"].AutoFitColumns();
                    sheet.Cells["M1:MN"].AutoFitColumns();


                    //sheet.Column(3).Style.Numberformat.Format = "$#,##0.00";
                    sheet.Column(2).Style.Numberformat.Format = "$#,##0.00";
                    sheet.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    sheet.Column(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(4).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(5).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(6).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(7).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(8).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(9).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(10).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(11).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(12).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(13).Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    using (MemoryStream stream = new MemoryStream())
                    {
                        ZipOutputStream zipstream = new ZipOutputStream(stream);
                        zipstream.SetLevel(3); //0-9, 9 being the highest level of compression


                        // loops through the PDFs I need to create
                        var newEntry = new ZipEntry("DSBangKe.xlsx");
                        newEntry.DateTime = DateTime.Now;
                        zipstream.PutNextEntry(newEntry);
                        using (MemoryStream inStream = new MemoryStream(excelPackage.GetAsByteArray()))
                        {
                            StreamUtils.Copy(inStream, zipstream, new byte[4096]);
                            inStream.Close();
                            zipstream.CloseEntry();

                            zipstream.IsStreamOwner = false;    // False stops the Close also Closing the underlying stream.
                            zipstream.Close();          // Must finish the ZipOutputStream before using outputMemStream.
                            stream.Position = 0;

                            LogStream.Write<List<ModelHouseHoldList>>(MyUser.UserName,
                                CommonData.AppName,
                                GetFunction(),
                                "Trích xuất hộ dân cư ra excell - ExportData", householdListExcel);

                            return File(stream.ToArray(), "application/octet-stream", "DSBangKe-" + TenDB + ".zip");
                        }
                    }

                }


            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public ActionResult print(int? HOUSEHOLD_NUMBER, string HOUSEHOLD_NAME, int? NUM_OF_HH_MEMBER,
            string AREA_ID, string ADDRESS_ID, string SURVEY_METHOD_ID, int? page)
        {
            if (String.IsNullOrEmpty(AREA_ID) || AREA_ID == "undefined")
                AREA_ID = null;
            if (String.IsNullOrEmpty(ADDRESS_ID) || ADDRESS_ID == "undefined")
                ADDRESS_ID = null;
            if (String.IsNullOrEmpty(SURVEY_METHOD_ID) || SURVEY_METHOD_ID == "undefined")
                SURVEY_METHOD_ID = null;
            if (String.IsNullOrEmpty(HOUSEHOLD_NAME) || HOUSEHOLD_NAME == "undefined")
                HOUSEHOLD_NAME = null;

            try
            {
                SearchHouseHoldModel search = new SearchHouseHoldModel();
                search.HOUSEHOLD_CODE = String.Empty;
                search.HOUSEHOLD_NAME = HOUSEHOLD_NAME;
                search.HOUSEHOLD_NUMBER = HOUSEHOLD_NUMBER;
                search.NUM_OF_HH_MEMBER = NUM_OF_HH_MEMBER;
                search.AREA_ID = AREA_ID;
                search.ADDRESS_ID = ADDRESS_ID;
                search.SURVEY_METHOD_ID = SURVEY_METHOD_ID;
                search.CODE = MyUser.AdminCode;
                string TenDB = MyUser.AdminName;
                if (page == null)
                {
                    search.PageIndex = 1;
                }
                else
                    search.PageIndex = page;

                search.PageSize = 999999;


                List<HouseHoldModel> householdList = new List<HouseHoldModel>();
                List<BangKeViewModel> household = new List<BangKeViewModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "SearchHouseHold");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);
                    string householdDownload = wc.UploadString(houseHoldServiceUrl + "SearchHouseHold", "POST", json);
                    householdList = JsonConvert.DeserializeObjectAsync<List<Models.HouseHoldModel>>(householdDownload).Result;

                    using (WebClient wc1 = new WebClient())
                    {
                        Helper.SetHeaderInfo(wc1, "GetHouseHoldAddressByID");
                        foreach (var h in householdList)
                        {
                            string householdDownloadALL = wc.DownloadString(houseHoldServiceUrl + "GetHouseHoldAddressByID" + "/" + h.HOUSEHOLD_ID);
                            household.Add(JsonConvert.DeserializeObjectAsync<BangKeViewModel>(householdDownloadALL).Result);
                        }
                    }

                }
                //tao ra 1 list moi khi muon lay mot so cot nhat dinh
                //var list = new List<Tuple<string, string, string, string, string, string, string, string, string, string, string>>();
                ViewBag.rows = household;
                LogStream.Write<List<HouseHoldModel>>(MyUser.UserName,
                        CommonData.AppName,
                        GetFunction(),
                        "In hộ dân cư ra excell - print", householdList);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
            return View();
        }
        #endregion

        private string GetFunction()
        {
            return "Quản lý bảng kê - HouseHoldController";
        }
    }
}
